package info.marlan.util;



import info.marlan.sipgate.R;
import info.marlan.sipgate.model.HistoryEntry;
import info.marlan.sipgate.model.PhoneBookEntry;
import info.marlan.sipgate.model.SipgateAccount;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper {
	public static final String LOGTAG = "info.marlan.sipgate";
	public static final String DB_NAME = "sip.sqlite";
	public static final String DB_TABLE_HISTORY = "history";
	public static final String DB_TABLE_ACCOUNT = "account";
	public static final String DB_TABLE_PHONEBOOK = "phonebook";

	public static final int DB_VERSION = 1;

	private static final String CLASSNAME = DBHelper.class.getSimpleName();


	private static final String[] COLS_HISTORY = new String[] {
			HistoryEntry.ENTRYID.toLowerCase(),
			HistoryEntry.TIMESTAMP.toLowerCase(),
			HistoryEntry.TOS.toLowerCase(),
			HistoryEntry.LOCALURI.toLowerCase(),
			HistoryEntry.REMOTEURI.toLowerCase(),
			HistoryEntry.STATUS.toLowerCase(),
			"widgetid"};

	private static final String[] COLS_ACCOUNT = new String[] {
		SipgateAccount.WIDGETID.toLowerCase(),
		SipgateAccount.USERNAME.toLowerCase(),
		SipgateAccount.PASSWORD.toLowerCase(),
		SipgateAccount.NOTIFY.toLowerCase(),
		SipgateAccount.SIPGATEURI.toLowerCase(),
		SipgateAccount.NOTIFYSTATUS.toLowerCase(),
		SipgateAccount.NOTIFYTOS.toLowerCase(),
		SipgateAccount.ENDPOINT.toLowerCase()
		};
	
	private static final String[] COLS_PHONEBOOK = new String[] {
		PhoneBookEntry.ENTRYID.toLowerCase(),
		PhoneBookEntry.ENTRYHASH.toLowerCase(),
		PhoneBookEntry.ENTRY.toLowerCase(),
		PhoneBookEntry.WIDGETID.toLowerCase()
		};

	private SQLiteDatabase db;
	private final DBOpenHelper dbOpenHelper;
	private final Context context;

	public void addAccount(SipgateAccount account){
		ContentValues values = new ContentValues();
		values.put(COLS_ACCOUNT[0],account.getWidgetId());
		values.put(COLS_ACCOUNT[1],account.getUserName());
		values.put(COLS_ACCOUNT[2],account.getPassword());
		values.put(COLS_ACCOUNT[3],account.isNotify()?1:0);
		values.put(COLS_ACCOUNT[4],account.getSipgateUri());
		values.put(COLS_ACCOUNT[5],account.getNotifyStatus());
		values.put(COLS_ACCOUNT[6],account.getNotifyTos());
		values.put(COLS_ACCOUNT[7],account.getEndpoint());
		this.db.replace(DBHelper.DB_TABLE_ACCOUNT, null, values);
	}
	
	public void addPhoneBookEntry(PhoneBookEntry entry){
		ContentValues values = new ContentValues();
		values.put(COLS_PHONEBOOK[0],entry.getWidgetId() + entry.getEntryID());
		values.put(COLS_PHONEBOOK[1],entry.getEntryHash());
		values.put(COLS_PHONEBOOK[2],entry.getEntry());
		values.put(COLS_PHONEBOOK[3],entry.getWidgetId());
		
		this.db.replace(DBHelper.DB_TABLE_PHONEBOOK, null, values);
	}
	
	public void updateHistory(HistoryEntry history){
		ContentValues values = new ContentValues();
		values.put(COLS_HISTORY[0],history.getWidgetId() + history.getEntryID());
		values.put(COLS_HISTORY[1],history.getTimestamp().getTime());
		values.put(COLS_HISTORY[2],history.gettOS());
		values.put(COLS_HISTORY[3],history.getLocalUri());
		values.put(COLS_HISTORY[4],history.getRemoteUri());
		values.put(COLS_HISTORY[5],history.getStatus());
		values.put(COLS_HISTORY[6],history.getWidgetId());
		this.db.replace(DBHelper.DB_TABLE_HISTORY, null, values);
	}
	
	public PhoneBookEntry findPhoneBookEntry(String sWord, int widgetid){
		if(sWord==null){
			return null;
		}
		PhoneBookEntry ret = null;
		Cursor c = null;
		try {
			c = this.db.query(false, DBHelper.DB_TABLE_PHONEBOOK, DBHelper.COLS_PHONEBOOK,
					COLS_PHONEBOOK[2] +" like '%"+sWord+"%' and widgetid = ? ",new String[]{""+widgetid}, null, null, null,"1");
			int numRows = c.getCount();
			if(numRows==0){
				return null;
			}
			c.moveToFirst();
			ret = new PhoneBookEntry();
			
			ret.setEntryID(c.getString(0).replaceFirst(""+c.getInt(3), ""));
			ret.setEntryHash(c.getString(1));
			ret.setEntry(c.getString(2));
			ret.setWidgetId(c.getInt(3));
			
		} catch (SQLException e) {
			Log.v(LOGTAG, DBHelper.CLASSNAME, e);
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}
		return ret;
	}
	
	public List<SipgateAccount> getAccounts(){
		List<SipgateAccount> ret = null;
		Cursor c = null;
		try {
			c = this.db.query(false, DBHelper.DB_TABLE_ACCOUNT, DBHelper.COLS_ACCOUNT,
					null,null, null, null, null,null);
			int numRows = c.getCount();
			if(numRows==0){
				return null;
			}
			c.moveToFirst();
			ret = new ArrayList<SipgateAccount>();
			for (int i = 0; i < numRows; ++i) {
				SipgateAccount acc = new SipgateAccount();
				acc.setWidgetId(c.getInt(0));
				acc.setUserName(c.getString(1));
				acc.setPassword(c.getString(2));
				acc.setNotify(c.getInt(3)==1?true:false);
				acc.setSipgateUri(c.getString(4));
				acc.setNotifyStatus(c.getInt(5));
				acc.setNotifyTos(c.getInt(6));
				acc.setEndpoint(c.getString(7));
				ret.add(acc);
				c.moveToNext();
			}
			
		} catch (SQLException e) {
			Log.v(LOGTAG, DBHelper.CLASSNAME, e);
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}
		return ret;
	}
	public void deleteAccount(int widgetId){
		this.db.delete(DBHelper.DB_TABLE_ACCOUNT, " widgetid=? ",new String[]{ ""+ widgetId});
		this.cleanUpDB();
	}
	
	
	
	public void cleanUpDB(){
		this.db.delete(DBHelper.DB_TABLE_HISTORY, " widgetid not in (select distinct widgetid from "+DBHelper.DB_TABLE_ACCOUNT+") ",null);
		this.db.delete(DBHelper.DB_TABLE_PHONEBOOK, " widgetid not in (select distinct widgetid from "+DBHelper.DB_TABLE_ACCOUNT+") ",null);
	}
	
	public SipgateAccount getAccount(int widgetId){
		SipgateAccount ret = null;
		Cursor c = null;
		try {
			c = this.db.query(false, DBHelper.DB_TABLE_ACCOUNT, DBHelper.COLS_ACCOUNT,
					" widgetid=? ",new String[]{ ""+ widgetId}, null, null, null,"1");
			int numRows = c.getCount();
			if(numRows==0){
				return null;
			}
			c.moveToFirst();
			ret = new SipgateAccount();
			
			ret.setWidgetId(c.getInt(0));
			ret.setUserName(c.getString(1));
			ret.setPassword(c.getString(2));
			ret.setNotify(c.getInt(3)==1?true:false);
			ret.setSipgateUri(c.getString(4));
			ret.setNotifyStatus(c.getInt(5));
			ret.setNotifyTos(c.getInt(6));
			ret.setEndpoint(c.getString(7));
		} catch (SQLException e) {
			Log.v(LOGTAG, DBHelper.CLASSNAME, e);
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}
		return ret;
	}




	public HistoryEntry getLatestHistoryEntry(int widgetid){
		HistoryEntry ret = new HistoryEntry();
		Cursor c = null;
		try {
			c = this.db.query(false, DBHelper.DB_TABLE_HISTORY, DBHelper.COLS_HISTORY,
					" widgetid = ? ", new String[]{""+widgetid}, null, null, COLS_HISTORY[1] + " desc ","1");
			int numRows = c.getCount();
			if(numRows==0){
				return null;
			}
			c.moveToFirst();
			
			for (int i = 0; i < numRows; ++i) {
				
				ret.setEntryID(c.getString(0).replaceFirst(""+c.getInt(6), ""));
				ret.setTimestamp(new Date(c.getLong(1)));
				ret.settOS(c.getString(2));
				ret.setLocalUri(c.getString(3));
				ret.setRemoteUri(c.getString(4));
				ret.setStatus(c.getString(5));
				ret.setWidgetId(c.getInt(6));
				c.moveToNext();
			}
		} catch (SQLException e) {
			Log.v(LOGTAG, DBHelper.CLASSNAME, e);
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}
		return ret;
	}

	public List<HistoryEntry> getHistoryEntries(Date from, int widgetid) {
		ArrayList<HistoryEntry> ret = new ArrayList<HistoryEntry>();
		Cursor c = null;
		try {
			c = this.db.query(DBHelper.DB_TABLE_HISTORY, DBHelper.COLS_HISTORY,
					"date > ? and widgetid = ? ", new String[]{ConvertUtil.getSQLTimestamp(from),""+widgetid}, null, null, null);
			int numRows = c.getCount();
			c.moveToFirst();
			for (int i = 0; i < numRows; ++i) {
				HistoryEntry entry = new HistoryEntry();
				entry.setEntryID(c.getString(0).replaceFirst(""+c.getInt(6), ""));
				entry.setTimestamp(new Date(c.getLong(1)));
				entry.settOS(c.getString(2));
				entry.setLocalUri(c.getString(3));
				entry.setRemoteUri(c.getString(4));
				entry.setStatus(c.getString(5));
				entry.setWidgetId(c.getInt(6));
				ret.add(entry);
				c.moveToNext();
			}
		} catch (SQLException e) {
			Log.v(LOGTAG, DBHelper.CLASSNAME, e);
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}
		return ret;
	}
	
	
	
	public int countHistoryEntries(int widgetid){

		Cursor c = null;
		try {
			c = this.db.rawQuery("SELECT count (entryid) as c FROM history where widgetid=? ",new String[]{""+widgetid});
			int numRows = c.getCount();
			if(numRows==0){
				return 0;
			}
			c.moveToFirst();
			return c.getInt(0);
		
			
		} catch (SQLException e) {
			Log.v(LOGTAG, DBHelper.CLASSNAME, e);
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}
		return 0;
	}
	
	
	
	public List<HistoryEntry> getHistoryEntries(int limit, int offset,int widgetid) {
		ArrayList<HistoryEntry> ret = new ArrayList<HistoryEntry>();
		Cursor c = null;
		try {
			
			StringBuilder q = new StringBuilder();
					q.append(" select ");
					q.append(COLS_HISTORY[0]).append(",").
					append(COLS_HISTORY[1]).append(",").
					append(COLS_HISTORY[2]).append(",").
					append(COLS_HISTORY[3]).append(",").
					append(COLS_HISTORY[4]).append(",").
					append(COLS_HISTORY[5]).append(",").
					append(COLS_HISTORY[6]).
					append(" from ").append(DBHelper.DB_TABLE_HISTORY).append(" where widgetid=").append(widgetid);
					q.append(" order by ").append(COLS_HISTORY[1]).append(" desc ");
					q.append(" LIMIT ").append(limit).append(" OFFSET ").append(offset);
			
			c = this.db.rawQuery(
					q.toString(), 
					null);
			int numRows = c.getCount();
			c.moveToFirst();
			for (int i = 0; i < numRows; ++i) {
				HistoryEntry entry = new HistoryEntry();
				entry.setEntryID(c.getString(0).replaceFirst(""+c.getInt(6), ""));
				entry.setTimestamp(new Date(c.getLong(1)));
				entry.settOS(c.getString(2));
				entry.setLocalUri(c.getString(3));
				entry.setRemoteUri(c.getString(4));
				entry.setStatus(c.getString(5));
				entry.setWidgetId(c.getInt(6));
				ret.add(entry);
				c.moveToNext();
			}
		} catch (SQLException e) {
			Log.v(LOGTAG, DBHelper.CLASSNAME, e);
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}
		return ret;
	}
	

	private static class DBOpenHelper extends SQLiteOpenHelper {
		private final Context context;

		public DBOpenHelper(final Context context) {
			super(context, DBHelper.DB_NAME, null, DBHelper.DB_VERSION);
			this.context = context;
		}

		@Override
		public void onCreate(final SQLiteDatabase db) {
			//Log.v(LOGTAG,"DBOpenHelper#onCreate called");
			String[] sql = context.getString(R.string.create_db).split("\n");
			try {
				for (String s : sql) {
					if (s.trim().length() > 0) {
						db.execSQL(s);
					}
				}
			
			} catch (SQLException e) {
				Log.e(LOGTAG, DBHelper.CLASSNAME, e);
			}
		}


		@Override
		public void onUpgrade(final SQLiteDatabase db, final int oldVersion,
				final int newVersion) {
			
			String[] sql = context.getString(R.string.upgrade_db).split("\n");
			try {
				for (String s : sql) {
					if (s.trim().length() > 0) {
					//	Log.v(LOGTAG, s);
						db.execSQL(s);
					}
				}
			} catch (SQLException e) {
				Log.e(LOGTAG, DBHelper.CLASSNAME, e);
			}
			//this.onCreate(db);
		}
	
		
		private void fillDB(final SQLiteDatabase db, int resource) {
				
			   BufferedReader in = null;
		        try {
		            in = new BufferedReader(
		            		new InputStreamReader(
		            		context.getResources().openRawResource(resource),"UTF8" ));
		            String line;
		            while ((line = in.readLine()) != null){
		            	if (line.trim().length() > 0) {
		            	//	Log.v(LOGTAG, line);
		            	db.execSQL(line);
		            	}
		            }
		            
		        } catch (IOException e) {
		        	Log.e(LOGTAG, DBHelper.CLASSNAME, e);
		        } finally {
		        	if (in != null) {
		                try {
		                    in.close();
		                } catch (IOException e) {
		                    // Ignore
		                }
		            }

		        }
		}
	}

	

	public DBHelper(final Context context) {
		this.dbOpenHelper = new DBOpenHelper(context);
		this.establishDb();
		this.context = context;
	}

	private void establishDb() {
		if (this.db == null) {
			this.db = this.dbOpenHelper.getWritableDatabase();
		}
	}

	public void cleanup() {
		if (this.db != null) {
			this.db.close();
			this.db = null;
		}
	}


}